﻿Imports System.Data.OleDb

Public Class ChineseFoodDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim conn_Smile As New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        Dim sql_Smile = "SELECT * FROM UserLikes WHERE ChineseFood=@f1 AND Reader=@f2"
        Dim oleDbCmd_Smile As New OleDbCommand(sql_Smile, conn_Smile)
        oleDbCmd_Smile.Parameters.AddWithValue("@f1", Request.Params("ID"))
        oleDbCmd_Smile.Parameters.AddWithValue("@f2", User.Identity.Name)

        conn_Smile.Open()
        Dim dr = oleDbCmd_Smile.ExecuteReader()
        If dr.HasRows Then
            Img_Smile.Visible = True
            btn_Like.Visible = False
            btn_CancelLike.Visible = True
        Else
            Img_Smile.Visible = False
            btn_Like.Visible = True
            btn_CancelLike.Visible = False
        End If
        conn_Smile.Close()




        If IsPostBack Then Return 'if it is a post back, then we jump out of the function and do not run the rest below
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        Dim sql = "INSERT INTO ChineseFoodViewedByUsers (ChineseFoodfkid, TravellerName) VALUES (@f1, @f2)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

    Protected Sub btn_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Submit.Click
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        Dim sql = "INSERT INTO Comments (Author, Subject, Message, ChineseFoodID) VALUES (@f1, @f2, @f3, @f4)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_Subject.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Message.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))
        cmd.ExecuteNonQuery()
        conn.Close()

        If Not User.IsInRole("admin") Then 'if user is not a vip user, when he comment a food, he will be a vip
            Dim sql_becomeVIP = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES (@f1, @f2)"
            Dim cmd_becomeVIP = New OleDbCommand(sql_becomeVIP, conn)
            conn.Open()
            cmd_becomeVIP.Parameters.AddWithValue("@f1", Membership.GetUser(User.Identity.Name).ProviderUserKey)
            cmd_becomeVIP.Parameters.AddWithValue("@f2", "1")
            cmd_becomeVIP.ExecuteNonQuery()
            conn.Close()
        End If

        Response.Redirect("ChineseFoodDetail.aspx?ID=" & Request.Params("ID"))
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim chineseFoodId = Request.Params("ID")
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        conn.Open()

        If tb_NewTags.Text <> "" Then
            Dim newTags = tb_NewTags.Text.Split(",")
            For Each newTag As String In newTags
                Dim isTagThereCmd = New OleDbCommand("SELECT * FROM Tags WHERE Tag = @f1", conn)
                isTagThereCmd.Parameters.AddWithValue("@f1", newTag)
                Dim datareader = isTagThereCmd.ExecuteReader()
                If Not datareader.HasRows Then
                    Dim addTagCmd = New OleDbCommand("INSERT INTO Tags (Tag) VALUES (@f1)", conn)
                    addTagCmd.Parameters.AddWithValue("@f1", newTag)
                    addTagCmd.ExecuteNonQuery()


                End If
                Dim findTagIDCmd = New OleDbCommand("SELECT ID FROM Tags WHERE Tag = @f1", conn)
                findTagIDCmd.Parameters.AddWithValue("@f1", newTag)
                Dim newTagDatareader = findTagIDCmd.ExecuteReader()
                newTagDatareader.Read()
                Dim newID = newTagDatareader.Item("ID")
                Dim makeAssociationCmd = New OleDbCommand("INSERT INTO ChineseFoodTagJunction (tagfkid, chinesefoodfkid) VALUES (@f1, @f2)", conn)
                makeAssociationCmd.Parameters.AddWithValue("@f1", newID)
                makeAssociationCmd.Parameters.AddWithValue("@f2", chineseFoodId)
                makeAssociationCmd.ExecuteNonQuery()
            Next
        End If

        For Each li As ListItem In CheckBoxList1.Items
            If (li.Selected = True) Then
                Dim sql = "INSERT INTO ChineseFoodTagJunction (tagfkid, chinesefoodfkid) VALUES (@f1, @f2)"
                Dim cmd = New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@f1", li.Value)
                cmd.Parameters.AddWithValue("@f2", chineseFoodId)
                cmd.ExecuteNonQuery()
            End If
        Next
        conn.Close()
    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Rating.Click
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        Dim rmSql = "DELETE FROM ChineseFoodRating WHERE Rater=@f1 AND ChineseFoodFkid=@f2"
        Dim rmCmd As New OleDbCommand(rmSql, conn)
        conn.Open()
        rmCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        rmCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        rmCmd.ExecuteNonQuery()
        conn.Close()



        Dim sql = "INSERT INTO ChineseFoodRating (Rating, ChineseFoodFkid, Rater) VALUES (@f1, @f2, @f3)"
        Dim addCmd As New OleDbCommand(sql, conn)
        conn.open()
        addCmd.Parameters.AddWithValue("@f1", Rating1.CurrentRating)
        addCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        addCmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        addCmd.ExecuteNonQuery()
        conn.Close()


    End Sub

    Protected Sub btn_Like_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Like.Click

            btn_Like.Visible = False
            Img_Smile.Visible = True
            btn_CancelLike.Visible = True

            Dim conn_Smile As New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
            Dim sql_Smile = "INSERT INTO UserLikes (ChineseFood, Reader) VALUES (@f1, @f2)"
            Dim oleDbCmd_Smile As New OleDbCommand(sql_Smile, conn_Smile)

            oleDbCmd_Smile.Parameters.AddWithValue("@f1", Request.Params("ID"))
            oleDbCmd_Smile.Parameters.AddWithValue("@f2", User.Identity.Name)

            conn_Smile.Open()
            oleDbCmd_Smile.ExecuteNonQuery()
            conn_Smile.Close()

            conn_Smile.Dispose()
            oleDbCmd_Smile.Dispose()

    End Sub


    Protected Sub btn_CancelLike_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_CancelLike.Click

        btn_Like.Visible = True
        Img_Smile.Visible = False
        btn_CancelLike.Visible = False

        Dim conn_Smile As New OleDbConnection(ConfigurationManager.ConnectionStrings("ChineseFoodConn").ConnectionString)
        Dim sql_Smile = "DELETE FROM UserLikes WHERE ChineseFood=@f1 AND Reader=@f2"
        Dim oleDbCmd_Smile As New OleDbCommand(sql_Smile, conn_Smile)

        oleDbCmd_Smile.Parameters.AddWithValue("@f1", Request.Params("ID"))
        oleDbCmd_Smile.Parameters.AddWithValue("@f2", User.Identity.Name)

        conn_Smile.Open()
        oleDbCmd_Smile.ExecuteNonQuery()
        conn_Smile.Close()

        conn_Smile.Dispose()
        oleDbCmd_Smile.Dispose()


    End Sub
End Class